﻿using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Employees : System.Web.UI.Page
{
    SqlConnection con;
    SqlDataAdapter da;
    DataSet ds;
    SqlCommandBuilder cb;
    DataTable dt;
    DataView dv;

    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (!IsPostBack)
            {
                Session["SearchEmployees"] = false;
                ViewState["Sort"] = "ASC";
                ViewState["mgr"] = false;
            }
            if (!(bool)Session["SearchEmployees"])
                loadEmployees();
        }
        catch (Exception)
        {

            Response.Write("<script>alert('" + "Load failed" + "')</script>");
            Response.Redirect("404.aspx");
        }




    }
    void InitialDBEmployee()
    {
        try
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "getEmployees";
            da = new SqlDataAdapter(cmd);
            cb = new SqlCommandBuilder(da);
            ds = new DataSet();
            da.FillSchema(ds, SchemaType.Mapped);
            da.Fill(ds, "HR.Employees");
        }
        catch
        {
            Response.Write("<script>alert('" + "Load failed" + "')</script>");
            Response.Redirect("404.aspx");
        }

    }
    void loadEmployees()
    {
        try
        {
            InitialDBEmployee();
            dt = ds.Tables["HR.Employees"];
            dv = new DataView(dt);
            gvEmployees.AutoGenerateSelectButton = true;
            gvEmployees.AllowPaging = true;
            gvEmployees.PageSize = 10;
            gvEmployees.DataSource = dt;
            gvEmployees.DataBind();
        }
        catch
        {
            Response.Write("<script>alert('" + "Load failed" + "')</script>");
            Response.Redirect("404.aspx");
            gvEmployees = null;
        }
    }
    void NewEmployee()
    {
        try
        {
            loadEmployees();
            lbError.Text = "";
            btnNew.Enabled = true;
            btnAdd.Enabled = true;
            btnSearch.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            txtID.Text = null;
            txtLastname.Text = null;
            txtFirstname.Text = null;
            txtTitle.Text = null;
            txtAddress.Text = null;
            txtCity.Text = null;
            txtRegion.Text = null;
            DDLMgrID.SelectedIndex = -1;
            txtPostcode.Text = null;
            DDLToC.SelectedIndex = -1;
            txtPhone.Text = null;
            txtDateOfBirth.Text = null;
            txtDateOfHire.Text = null;
            DDLCountry.SelectedIndex = -1;
            DDLMgrID.Visible = false;
            ViewState["mgr"] = false;
        }
        catch
        {
        }
    }
    void addNewEmployee()
    {
        try
        {
            dt = ds.Tables["HR.Employees"];
            DataRow r = dt.NewRow();
            r["lastname"] = txtLastname.Text;
            r["firstname"] = txtFirstname.Text;
            r["title"] = txtTitle.Text;
            r["titleofcourtesy"] = DDLToC.Text;
            r["address"] = txtAddress.Text;
            r["city"] = txtCity.Text;
            if (txtRegion.Text != "")
            {
                r["region"] = txtRegion.Text;
            }
            else
                r["region"] = DBNull.Value;
            if (txtPostcode.Text != "")
            {
                r["postalcode"] = txtPostcode.Text;
            }
            else
                r["postalcode"] = DBNull.Value;
            r["country"] = DDLCountry.Text;
            if (txtPhone.Text != "")
            {
                r["phone"] = txtPhone.Text;
            }
            else
                r["phone"] = DBNull.Value;
            DateTime dt1;
            DateTime.TryParse(txtDateOfBirth.Text, out dt1);
            r["birthdate"] = dt1;
            DateTime.TryParse(txtDateOfHire.Text, out dt1);
            r["hiredate"] = dt1;
            if ((bool)ViewState["mgr"])
                r["mgrid"] = this.DDLMgrID.SelectedValue;
            else
                r["mgrid"] = DBNull.Value;
            dt.Rows.Add(r);
            da.Update(dt);
            NewEmployee();
        }
        catch
        {
            Response.Write("<script>alert('" + "Add failed" + "')</script>");
        }
    }
    void updateEmployee()
    {
        try
        {
            DataRow[] r;
            if (!(bool)Session["SearchEmployees"])
            {
                dt = ds.Tables["HR.Employees"];
                GridViewRow rv = gvEmployees.SelectedRow;
                r = dt.Select(string.Format("empid = '{0}'", rv.Cells[1].Text));
            }
            else
            {
                loadEmployees();
                dt = ds.Tables["HR.Employees"];
                r = dt.Select(string.Format("empid = '{0}'", txtID.Text));
            }

            r[0]["empid"] = this.txtID.Text;
            r[0]["lastname"] = this.txtLastname.Text;
            r[0]["firstname"] = this.txtFirstname.Text;
            r[0]["title"] = this.txtTitle.Text;
            r[0]["titleofcourtesy"] = this.DDLToC.Text;
            DateTime dt1;
            DateTime.TryParse(txtDateOfBirth.Text, out dt1);
            r[0]["birthdate"] = dt1;
            DateTime.TryParse(txtDateOfHire.Text, out dt1);
            r[0]["hiredate"] = dt1;

            r[0]["address"] = this.txtAddress.Text;
            r[0]["city"] = this.txtCity.Text;
            if (txtRegion.Text != "")
            {
                r[0]["region"] = this.txtRegion.Text;
            }
            else
                r[0]["region"] = this.txtRegion.Text;
            if (txtPostcode.Text == "")
            {
                r[0]["postalcode"] = this.txtPostcode.Text;
            }
            else
                r[0]["postalcode"] = this.txtPostcode.Text;
            r[0]["country"] = this.DDLCountry.Text;
            if (txtPhone.Text == "")
            {
                r[0]["phone"] = this.txtPhone.Text;
            }
            else
                r[0]["phone"] = this.txtPhone.Text;
            if ((bool)ViewState["mgr"])
                r[0]["mgrid"] = this.DDLMgrID.SelectedValue;
            else
                r[0]["mgrid"] = DBNull.Value;
            da.Update(dt);
        }
        catch
        {
            Response.Write("<script>alert('" + "Update failed" + "')</script>");
        }
        finally
        {
            if (!(bool)Session["SearchEmployees"])
                NewEmployee();
            else
                search();
        }
    }

    void deleteEmployee()
    {
        try
        {
            string mgr = "";
            if (!(bool)Session["SearchEmployees"])
            {

                GridViewRow rv = gvEmployees.SelectedRow;
                dv = new DataView(dt);
                mgr = rv.Cells[1].Text;
                dv.RowFilter = string.Format("empid = '{0}'", rv.Cells[1].Text);
            }
            else
            {
                loadEmployees();
                dt = ds.Tables["HR.Employees"];
                dv = new DataView(dt);
                dv.RowFilter = string.Format("empid = '{0}'", txtID.Text);
                mgr = txtID.Text;
            }
            DataRow[] r = dt.Select(string.Format("mgrid = '{0}'", mgr));
            if (r.Length != 0)
                r[0]["mgrID"] = DBNull.Value;
            da.Update(dt);
            dv.Delete(0);
            da.Update(dt);
        }
        catch
        {
            Response.Write("<script>alert('" + "Delete failed" + "')</script>");
        }
        finally
        {
            NewEmployee();
        }

    }
    void searchEmployee()
    {
        try
        {
            txtID.Text = null;
            txtLastname.Text = null;
            txtFirstname.Text = null;
            txtTitle.Text = null;
            txtAddress.Text = null;
            txtCity.Text = null;
            txtRegion.Text = null;
            DDLMgrID.SelectedIndex = -1;
            txtPostcode.Text = null;
            DDLToC.SelectedIndex = -1;
            txtPhone.Text = null;
            txtDateOfBirth.Text = null;
            txtDateOfHire.Text = null;
            DDLCountry.SelectedIndex = -1;
            DDLMgrID.Visible = false;
            ViewState["mgr"] = false;
            string searchMethod = DDLSearchMethod.Text;
            string item = txtSearch.Text;
            if (!(bool)Session["SearchEmployees"]) //is searching?
            {
                if (item != "" && ((((searchMethod == "ID") || (searchMethod == "mgrID")) && isNumber(item)) || ((searchMethod != "ID") && (searchMethod != "mgrID"))))
                {
                    txtSearch.Enabled = false;
                    DDLSearchMethod.Enabled = false;
                    search();
                }
                else
                {
                    lbError.Text = "Must be number";
                }

            }
            else
            {
                Session["SearchEmployees"] = false;
                btnSearch.Text = "Search by";
                txtSearch.Enabled = true;
                txtSearch.Text = "";
                DDLSearchMethod.Enabled = true;
                btnNew.Enabled = true;
                btnAdd.Enabled = true;
                NewEmployee();
            }
        }
        catch
        {
            Response.Write("<script>alert('" + "Search failed" + "')</script>");
        }
    }
    void search()
    {
        try
        {
            dv = new DataView(dt);
            string searchMethod = DDLSearchMethod.Text;
            string item = txtSearch.Text;
            btnSearch.Text = "Cancel";
            btnNew.Enabled = false;
            btnAdd.Enabled = false;
            Session["SearchEmployees"] = true; //flag for switch to Cancel

            switch (searchMethod)
            {
                case "ID":
                    dv.RowFilter = string.Format("empid = '{0}'", item);
                    gvEmployees.DataSource = dv;
                    gvEmployees.DataBind();
                    break;
                case "name":
                    dv.RowFilter = string.Format("firstname + ' ' + lastname like '%{0}%'", item);
                    gvEmployees.DataSource = dv;
                    gvEmployees.DataBind();
                    break;
                case "title":
                    dv.RowFilter = string.Format("title like '%{0}%'", item);
                    gvEmployees.DataSource = dv;
                    gvEmployees.DataBind();
                    break;
                case "country":
                    dv.RowFilter = string.Format("country like '%{0}%'", item);
                    gvEmployees.DataSource = dv;
                    gvEmployees.DataBind();
                    break;
                case "mgrID":
                    dv.RowFilter = string.Format("mgrid = '{0}'", item);
                    gvEmployees.DataSource = dv;
                    gvEmployees.DataBind();
                    break;
            }
            gvEmployees.PageIndex = 0;
        }
        catch
        {

            Response.Write("<script>alert('" + "Search failed" + "')</script>");
        }
    }
    bool isNumber(string s)
    {
        foreach (char c in s)
        {
            if (!char.IsDigit(c))
                return false;
        }
        return true;
    }



    protected void gvEmployees_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        try
        {
            gvEmployees.PageIndex = e.NewPageIndex;
            gvEmployees.DataBind();

        }
        catch
        {
        }
    }
    bool validDateTime()
    {
        try
        {
            DateTime dateOfBirth = DateTime.Parse(txtDateOfBirth.Text);
            int birthYear = dateOfBirth.Year;
            DateTime dateOfHire = DateTime.Parse(txtDateOfHire.Text);
            int HireYear = dateOfHire.Year;
            if (HireYear - birthYear < 18)
                return false;
            return true;
        }catch{
            return false;
        }
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        if (validDateTime())
        {
            lbdate.Text = "";
            updateEmployee();
        }
        else
        {
            lbdate.Text = "must be > 18 for hiring";
        }
 
    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        NewEmployee();
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        if (validDateTime())
        {
            lbdate.Text = "";
            addNewEmployee();
        }
        else
        {
            lbdate.Text = "must be > 18 for hiring";
        }
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        deleteEmployee();
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        if (txtSearch.Text != "" || (bool)Session["SearchEmployees"])
        {
           
            lbError.Text = "";
            searchEmployee();
        }
        else
        {
            lbError.Text = "require";
        }
    }


    protected void txtSearch_TextChanged(object sender, EventArgs e)
    {
        lbError.Text = "";
    }
    protected void gvEmployees_Sorting(object sender, GridViewSortEventArgs e)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        SqlDataAdapter da = new SqlDataAdapter("getEmployees", con);
        DataTable dt = new DataTable("HR.Employees");
        da.Fill(dt);
        gvEmployees.AutoGenerateSelectButton = true;
        gvEmployees.AllowPaging = true;
        gvEmployees.PageSize = 10;
        DataView dv = new DataView(dt);
        dv.Sort = e.SortExpression + " " + (string)ViewState["Sort"];
        if ((string)ViewState["Sort"] == "ASC")
        {
            ViewState["Sort"] = "DESC";
        }
        else
        {
            ViewState["Sort"] = "ASC";
        }

        dt = dv.ToTable();
        ViewState["isSort"] = true;
        gvEmployees.DataSource = dt;
        gvEmployees.DataBind();
    }
    protected void btnMgrID_Click(object sender, EventArgs e)
    {
        if (!(bool)ViewState["mgr"])
        {
            DDLMgrID.Visible = true;
            ViewState["mgr"] = true;
        }
        else
        {
            DDLMgrID.Visible = false;
            ViewState["mgr"] = false;
        }
    }
    protected void gvEmployees_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {

            btnNew.Enabled = true;
            btnUpdate.Enabled = true;
            btnDelete.Enabled = true;
            btnAdd.Enabled = false;
            GridViewRow r = gvEmployees.SelectedRow;

            txtID.Text = Server.HtmlDecode(r.Cells[1].Text);
            txtLastname.Text = Server.HtmlDecode(r.Cells[3].Text);
            txtFirstname.Text = Server.HtmlDecode(r.Cells[4].Text);
            txtTitle.Text = Server.HtmlDecode(r.Cells[5].Text);
            DDLToC.SelectedValue = Server.HtmlDecode(r.Cells[2].Text);
            txtDateOfBirth.Text = Server.HtmlDecode(r.Cells[6].Text);
            txtDateOfHire.Text = Server.HtmlDecode(r.Cells[7].Text);
            txtAddress.Text = Server.HtmlDecode(r.Cells[8].Text);
            txtCity.Text = Server.HtmlDecode(r.Cells[10].Text);
            txtRegion.Text = Server.HtmlDecode(r.Cells[11].Text);
            txtPostcode.Text = Server.HtmlDecode(r.Cells[12].Text);
            DDLCountry.SelectedValue = Server.HtmlDecode(r.Cells[9].Text);
            txtPhone.Text = Server.HtmlDecode(r.Cells[13].Text);
            if (Server.HtmlDecode(r.Cells[14].Text).Trim().Length == 0)
            {
                DDLMgrID.Visible = false;
                ViewState["mgr"] = false;

            }
            else
            {
                DDLMgrID.Visible = true;
                ViewState["mgr"] = true;
                DDLMgrID.SelectedValue = Server.HtmlDecode(r.Cells[14].Text);
            }

        }
        catch
        {
            Response.Write("<script>alert('" + "Selected failed" + "')</script>");
        }
    }
}
